package com.lesofn.util.excel;

import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.LinkedBlockingQueue;


public class ExcelImportUtil<T> {

    private Class clazz;
    private  Field fields[];

    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 基于注解读取excel
     * @param is 文件上传的流信息
     * @param rowIndex 读取数据的起始行
     * @param cellIndex 读取数据的起始单元格位置
     */
    public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum();

            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                entity = (T) clazz.newInstance();
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if(j == ea.sort()) {
                                field.set(entity, covertAttrType(field, cell));
                            }
                        }
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 基于注解读取excel
     * @param is 文件上传的流信息
     * @param rowIndex 读取数据的起始行
     * @param cellIndex 读取数据的起始单元格位置
     * @return map<sheetName, sheet内每一行封装的实体对象>
     */
    public Map<String, List<T>> readExcelAllSheet(InputStream is, int rowIndex, int cellIndex) {
        Map<String, List<T>> map = new ConcurrentHashMap();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            int numberOfSheets = workbook.getNumberOfSheets();
            List<CompletableFuture> cfList = new ArrayList<>();
            for( int sheetNum = 0; sheetNum < numberOfSheets; sheetNum ++){
                System.out.println("开始读取第"+sheetNum+"个sheet页");
                BlockingQueue blockingQueue = new LinkedBlockingQueue();
                // 同步方式读取
                read(sheetNum, rowIndex, cellIndex, workbook, map);
                // 异步方式读取
                CompletableFuture  cf = CompletableFuture.runAsync(new AsyncReader(sheetNum, rowIndex, cellIndex, workbook, map));
                cfList.add(cf);
            }
            CompletableFuture.allOf(cfList.toArray(new CompletableFuture[cfList.size()])).join();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    class AsyncReader<T> implements Runnable{
        int sheetNum;
        int rowIndex;
        int cellIndex;
        XSSFWorkbook workbook;
        Map<String, List<T>> map;

        public AsyncReader(int sheetNum, int rowIndex, int cellIndex, XSSFWorkbook workbook, Map<String, List<T>> map) {
            this.sheetNum = sheetNum;
            this.rowIndex = rowIndex;
            this.cellIndex = cellIndex;
            this.workbook = workbook;
            this.map = map;
        }

        @Override
        public void run() {
            read(sheetNum, rowIndex, cellIndex, workbook, map);
        }
    }

    protected <T> List<T> read(int sheetNum, int rowIndex, int cellIndex, XSSFWorkbook workbook, Map<String, List<T>>  map) {
        List<T> list = new ArrayList<>();
        try {
            T entity;
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 读取列头
            Row headRow = sheet.getRow(rowIndex - 1);
            Map<Integer, String> headMaps = new HashMap<>();
            for (int j = cellIndex; j < headRow.getLastCellNum(); j++) {
                Cell cell = headRow.getCell(j);
                String headName = getValue(cell);
                headMaps.put(j, headName);
            }
            // 读取内容
            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                entity = (T) clazz.newInstance();
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
    //                                if(j == ea.sort()) {
                            if(Objects.equals(headMaps.get(j), ea.name())) {
                                field.set(entity, covertAttrType(field, cell));
                            }
                        }
                    }
                }
//                Thread.sleep(1000);
                System.out.println(">>>" + entity);
                list.add(entity);
            }
            map.put(sheet.getSheetName(), list);
            return list;
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList();
    }

    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private static Object covertAttrType(Field field, Cell cell) throws ParseException {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }


    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = BigDecimal.valueOf(num);
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}
